package com.springboot.demo.mapper;

import com.alibaba.fastjson.JSONObject;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.springboot.demo.entity.*;
import com.springboot.demo.exception.BusinessException;
import com.springboot.demo.service.TableService;
import com.springboot.demo.utils.Constant;
import com.springboot.demo.utils.DateUtils;
import com.springboot.demo.utils.ResponseResult;
import org.apache.ibatis.jdbc.SQL;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.core.context.SecurityContextHolder;
import org.springframework.util.StringUtils;

import java.util.*;

/**
 * @author wubc683
 * @desc
 * @date 2021-09-01 16:40
 */
public class SqlMapper {
    @Autowired
    TableService tableService;

    /**
     * 多条件查询收件箱消息
     *
     * @param uid
     * @param readed
     * @param type
     * @param startDate
     * @param endDate
     * @param search
     * @param sort
     * @return: java.lang.String
     */
    public String selectInbox(Long uid, Integer readed, Integer type, Date startDate, Date endDate, String search,
                              String sort) {
        SQL sql = new SQL();
        sql.SELECT("i.id,i.ownerId,i.type,i.content,i.readed,i.createDate,i.needReply,i.replyState,i.replyUrl");
        sql.FROM("web_inbox i");
        if (type != null) {
            sql.AND().WHERE("i.type = #{type} ");
        }
        if (readed != null) {
            sql.AND().WHERE( "i.readed = #{readed}");
        }
        if (search != null && !"".equals(search)) {
            sql.AND().WHERE( "concat(ifnull(i.content,'')) like concat('%',#{search}," +
                    "'%') ");
        }
        if (startDate != null) {
            sql.AND().WHERE( "i.createDate >= #{startDate} ");
        }
        if (endDate != null) {
            sql.AND().WHERE( "i.createDate <= #{endDate} ");
        }
        sql.AND().WHERE("i.ownerId = #{uid} ");
        if (StringUtils.hasLength(sort)) {
            sql.ORDER_BY("${sort}");
        } else {
            sql.ORDER_BY("i.createDate desc");
        }
        return sql.toString();
    }

    /**
     * 获取设备群设备信息
     *
     * @param cid
     * @param search
     * @param state
     * @param sort
     * @return: java.lang.String
     */
    public String selectDevClusterDevInfo(Long cid, String search, Integer state, String sort) {
        SQL sql = new SQL();
        sql.SELECT("id,micoDeviceId,deviceClusterId,deviceName,macAddr," +
                "createDate,online,longitude,latitude,description,address,province,city,region,deleteFlag");
        sql.FROM("web_device");
        sql.WHERE("deviceClusterId = #{cid} and deleteFlag = 0");

        if (StringUtils.hasLength(search)) {
            sql.AND().WHERE("concat(ifnull(id,''),ifnull(deviceName,''),ifnull(createDate,'')," +
                    "ifnull(address,'')) " + "like " + "concat('%',#{search},'%')");
        }
        // state:0离线，1在线
        if (state != null) {
            sql.AND().WHERE(" online = #{state}");
        }

        if (StringUtils.hasLength(sort)) {
            sql.ORDER_BY("${sort}");
        } else {
            sql.ORDER_BY("createDate desc");
        }
        return String.valueOf(sql);
    }

    /**
     * 通过名字获取设备群信息
     *
     * @param userId
     * @param name
     * @return: java.lang.String
     */
    public String getDevClusterInfo(Long userId, String name) {
        SQL sql = new SQL();
        sql.SELECT_DISTINCT("dc.id as id,dc.name as name,dc.description as description,dc.createDate as createDate");
        sql.FROM("web_device_cluster dc");
        sql.LEFT_OUTER_JOIN("web_user_device_cluster udc on dc.id = udc.deviceClusterId");
        sql.LEFT_OUTER_JOIN("web_user u on u.id = udc.userId");
        sql.WHERE("((u.deleteFlag = '' and u.parentId = #{userId})" +
                " or u.id = #{userId}) and dc.name like concat('%',#{name},'%')");
        return String.valueOf(sql);
    }

    /**
     * 获取所有场景信息
     * @param uid
     * @param gid
     * @param sort
     * @param startDate
     * @param endDate
     * @param search
     * @return: java.lang.String
     */
    public String getSceneInfo(Long uid, Long gid, String sort, Date startDate,Date endDate,String search) {
        SQL sql = new SQL();
        sql.SELECT("id,sceneGroupId,name,state,ownerId,createDate,description,size,readWrite,address,longitude," +
                "latitude");
        sql.FROM("web_scene");
        sql.AND().WHERE("ownerId = #{uid}");
        if (gid != null) {
            sql.AND().WHERE("sceneGroupId = #{gid}");
        }
        if (StringUtils.hasLength(search)) {
            sql.AND().WHERE(" concat(ifnull(name,'')) " +
                    " like concat('%',#{search},'%')");
        }
        if (startDate != null) {
            sql.AND().WHERE("createDate >= #{startDate} ");
        }
        if (endDate != null) {
            sql.AND().WHERE( "createDate <= #{endDate} ");
        }
        if (StringUtils.hasLength(sort)) {
            sql.ORDER_BY("${sort}");
        } else {
            sql.ORDER_BY("createDate desc");
        }
        return String.valueOf(sql);
    }

    /**
     * 获取mico设备信息
     *
     * @param username
     * @param sort
     * @param search
     * @return: java.lang.String
     */
    public String getMicoDevInfo(String username, String sort, String search) {
        SQL sql = new SQL();
        sql.SELECT("distinct d.ID as deviceId,d.name as deviceName,d.macAddr as macAddr, dc.account as " +
                "devClusterName," +
        "udc.role as role ,wdc.name as importClusterName ,wu.username as username,wd.deleteFlag as deleteFlag");
        sql.FROM("T_Device d");
        sql.LEFT_OUTER_JOIN("web_device wd on wd.micoDeviceId = d.ID and d.status = 0");
        sql.LEFT_OUTER_JOIN("web_device_cluster wdc on wd.deviceClusterId = wdc.id");
        sql.LEFT_OUTER_JOIN("T_DeviceCluster dc on dc.ID = d.clusterID");
        sql.LEFT_OUTER_JOIN("T_User_DevCluster udc on udc.deviceClusterID = dc.ID");
        sql.LEFT_OUTER_JOIN("T_User u on u.ID = udc.userID");
        sql.LEFT_OUTER_JOIN("web_user_device_cluster wudc on wudc.deviceClusterId = wdc.id and wudc.role = 1");
        sql.LEFT_OUTER_JOIN("web_user wu on wu.id = wudc.userId");
        sql.WHERE("u.account = #{username}");
        if (StringUtils.hasLength(search)) {
            sql.AND().WHERE("concat(ifnull(d.name,''),ifnull(dc.account,''),ifnull(wdc.name,'')) " +
                    "like concat('%',#{search},'%')");
        }
        if (StringUtils.hasLength(sort)) {
            sql.ORDER_BY(" ${sort}");
        } else {
            sql.ORDER_BY(" d.ID asc");
        }
        return String.valueOf(sql);
    }

    /**
     * 获取报表指标卡sql
     *
     * @param table
     * @param tableFilterValueList
     * @return: java.lang.String
     */
    public String getIndexTableDataSql(Table table, List<TableFilterValue> tableFilterValueList) {
        SQL sql = new SQL();
        // 获取当前用户id
        Object principal = SecurityContextHolder.getContext().getAuthentication().getPrincipal();
        UserDetailsImpl userDetailsImpl = (UserDetailsImpl) principal;
        Long uid = userDetailsImpl.getUser().getId();

        // 获取数值字段名称
        String valueName = getFieldName(table.getDataTableType(), table.getValue());
        // 获取过滤条件sql
        String filterDataSql = getFilterDataSql(table, tableFilterValueList);
        String tableName = null;
        // 获取报表不同值sql
        String tableValueSql = getTableValueSql(table.getValueType(), valueName);
        sql.SELECT(tableValueSql);
        if (table.getDataTableType().equals(Constant.DataTable.USER)) {
            tableName = "web_user u";
            sql.FROM(tableName);
            sql.LEFT_OUTER_JOIN(" web_role r on u.rid = r.id");
            sql.WHERE("((u.deleteFlag = '' and u.parentId = " + uid + " ) or u.id = " + uid + ") " + filterDataSql);
        } else if (table.getDataTableType().equals(Constant.DataTable.DEVICE)) {
            tableName = "web_device d";
            sql.FROM(tableName);
            sql.LEFT_OUTER_JOIN("web_device_cluster dc on d.deviceClusterId = dc.id and d.deleteFlag = 0");
            sql.LEFT_OUTER_JOIN("web_user_device_cluster udc on udc.deviceClusterId = d.deviceClusterId and udc.role " +
                    "= 1"); // 设备群角色=1为群主身份，保证连接表唯一
            sql.LEFT_OUTER_JOIN("web_user u on u.id = udc.userId");
            sql.WHERE("u.id = " + uid + " " + filterDataSql);
        } else if (table.getDataTableType().equals(Constant.DataTable.SCENE)) {
            tableName = "web_scene s";
            sql.FROM(tableName);
            sql.LEFT_OUTER_JOIN("web_scene_group sg on sg.id = s.sceneGroupId");
            sql.LEFT_OUTER_JOIN("web_connection c on c.sceneId = s.id");
            sql.LEFT_OUTER_JOIN("web_device d on c.deviceId = d.id and d.deleteFlag = 0");
            sql.WHERE("s.ownerId = " + uid + " " + filterDataSql);
        } else if (table.getDataTableType().equals(Constant.DataTable.HISTORY_DATA)) {
            tableName = "web_history_data hd";
            sql.FROM(tableName);
            sql.LEFT_OUTER_JOIN("web_variable v on hd.varId = v.id");
            sql.LEFT_OUTER_JOIN("web_scene s on s.id = hd.sceneId");
            sql.LEFT_OUTER_JOIN("web_connection c on c.sceneId = s.id");
            sql.LEFT_OUTER_JOIN("web_device d on d.id = c.deviceId and d.deleteFlag = 0");
            sql.WHERE("hd.ownerId = " + uid + " " + filterDataSql);
        } else if (table.getDataTableType().equals(Constant.DataTable.HISTORY_ALARM)) {
            tableName = "web_history_alarm ha";
            sql.FROM(tableName);
            sql.LEFT_OUTER_JOIN("web_variable v on ha.varId = v.id");
            sql.LEFT_OUTER_JOIN("web_scene s on s.id = ha.sceneId");
            sql.LEFT_OUTER_JOIN("web_connection c on c.sceneId = s.id");
            sql.LEFT_OUTER_JOIN("web_device d on d.id = c.deviceId and d.deleteFlag = 0");
            sql.WHERE("ha.ownerId = " + uid + " " + filterDataSql);
        }
        return sql.toString();
    }

    /**
     * 获取报表列表sql
     *
     * @param table
     * @param tableCompareField
     * @param tableFilterValueList
     * @param sort
     * @return: java.lang.String
     */
    public String getTableTableDataSql(Table table, List<String> tableCompareField,
                                       List<TableFilterValue> tableFilterValueList, String sort) {
        // 获取当前用户id
        Object principal = SecurityContextHolder.getContext().getAuthentication().getPrincipal();
        UserDetailsImpl userDetailsImpl = (UserDetailsImpl) principal;
        Long uid = userDetailsImpl.getUser().getId();

        // 获取过滤条件sql
        String filterDataSql = getFilterDataSql(table, tableFilterValueList);

        // 获取维度字段名称
        String dimensionName = getFieldName(table.getDataTableType(), table.getDimension());
        // 获取数值字段名称
        String valueName = getFieldName(table.getDataTableType(), table.getValue());
        // 获取对比字段名称
        String compareName = getFieldName(table.getDataTableType(), table.getCompare());
        String tableName = null;
        SQL sql = new SQL();
        // 获取报表列表列的sql
        String tableTableValueSql = getTableTableValueSql(table.getValueType(), compareName, valueName, tableCompareField);
        sql.SELECT(dimensionName + " as dimension" + tableTableValueSql);
        if (table.getDataTableType().equals(Constant.DataTable.USER)) {
            tableName = "web_user u";
            sql.FROM(tableName);
            sql.LEFT_OUTER_JOIN(" web_role r on u.rid = r.id");
            sql.WHERE("((u.deleteFlag = '' and u.parentId = " + uid + " ) or u.id = " + uid + " ) and " +
                    dimensionName + " is not null " + filterDataSql);
        } else if (table.getDataTableType().equals(Constant.DataTable.DEVICE)) {
            tableName = "web_device d";
            sql.FROM(tableName);
            sql.LEFT_OUTER_JOIN("web_user_device_cluster udc on udc.deviceClusterId = d.deviceClusterId and d" +
                    ".deleteFlag = 0");
            sql.LEFT_OUTER_JOIN("web_user u on u.id = udc.userId");
            sql.LEFT_OUTER_JOIN("web_device_cluster dc on d.deviceClusterId = dc.id");
            sql.WHERE("udc.userId = " + uid + " and " +
                    dimensionName + " is not null " + filterDataSql);
        } else if (table.getDataTableType().equals(Constant.DataTable.SCENE)) {
            tableName = "web_scene s";
            sql.FROM(tableName);
            sql.LEFT_OUTER_JOIN("web_scene_group sg on sg.id = s.sceneGroupId");
            sql.LEFT_OUTER_JOIN("web_connection c on c.sceneId = s.id");
            sql.LEFT_OUTER_JOIN("web_device d on c.deviceId = d.id and d.deleteFlag = 0");
            sql.WHERE("s.ownerId = " + uid + " and " + dimensionName + " is not null " + filterDataSql);
        } else if (table.getDataTableType().equals(Constant.DataTable.HISTORY_DATA)) {
            tableName = "web_history_data hd";
            sql.FROM(tableName);
            sql.LEFT_OUTER_JOIN("web_variable v on hd.varId = v.id");
            sql.LEFT_OUTER_JOIN("web_scene s on s.id = hd.sceneId");
            sql.LEFT_OUTER_JOIN("web_connection c on c.sceneId = s.id");
            sql.LEFT_OUTER_JOIN("web_device d on d.id = c.deviceId and d.deleteFlag = 0");
            sql.WHERE("hd.ownerId = " + uid + " and " + dimensionName + " is not null " + filterDataSql);
        } else if (table.getDataTableType().equals(Constant.DataTable.HISTORY_ALARM)) {
            tableName = "web_history_alarm ha";
            sql.FROM(tableName);
            sql.LEFT_OUTER_JOIN("web_variable v on ha.varId = v.id");
            sql.LEFT_OUTER_JOIN("web_scene s on s.id = ha.sceneId");
            sql.LEFT_OUTER_JOIN("web_connection c on c.sceneId = s.id");
            sql.LEFT_OUTER_JOIN("web_device d on d.id = c.deviceId and d.deleteFlag = 0");
            sql.WHERE("ha.ownerId = " + uid + " and " + dimensionName + " is not null " + filterDataSql);
        }
        sql.GROUP_BY(dimensionName);
        if (StringUtils.hasLength(sort)) {
            sql.ORDER_BY(sort);
        }
        return sql.toString();
    }

    /**
     * 获取报表饼图sql
     *
     * @param table
     * @param tableFilterValueList
     * @return: java.lang.String
     */
    public String getTablePieDataSql(Table table, List<TableFilterValue> tableFilterValueList) {
        // 获取当前用户id
        Object principal = SecurityContextHolder.getContext().getAuthentication().getPrincipal();
        UserDetailsImpl userDetailsImpl = (UserDetailsImpl) principal;
        Long uid = userDetailsImpl.getUser().getId();

        // 获取过滤条件sql
        String filterDataSql = getFilterDataSql(table, tableFilterValueList);

        // 获取维度字段名称
        String dimensionName = getFieldName(table.getDataTableType(), table.getDimension());
        // 获取数值字段名称
        String valueName = getFieldName(table.getDataTableType(), table.getValue());
        String tableName = null;
        SQL sql = new SQL();
        // 获取报表不同值sql
        String tableValueSql = getTableValueSql(table.getValueType(), valueName);
        sql.SELECT(dimensionName + " as content ," + tableValueSql);
        if (table.getDataTableType().equals(Constant.DataTable.USER)) {
            tableName = "web_user u";
            sql.FROM(tableName);
            sql.LEFT_OUTER_JOIN(" web_role r on u.rid = r.id");
            sql.WHERE("((u.deleteFlag = '' and u.parentId = " + uid + ") or u.id = " + uid +
                    ") and " + dimensionName + " is not null and " + valueName + " is not null " + filterDataSql);
        } else if (table.getDataTableType().equals(Constant.DataTable.DEVICE)) {
            tableName = "web_device d";
            sql.FROM(tableName);
            sql.LEFT_OUTER_JOIN("web_user_device_cluster udc on udc.deviceClusterId = d.deviceClusterId and d.deleteFlag = 0");
            sql.LEFT_OUTER_JOIN("web_user u on u.id = udc.userId");
            sql.LEFT_OUTER_JOIN("web_device_cluster dc on d.deviceClusterId = dc.id");
            sql.WHERE("u.id = " + uid + " and " + dimensionName + " is not null and "
                    + valueName + " is not null " + filterDataSql);
        } else if (table.getDataTableType().equals(Constant.DataTable.SCENE)) {
            tableName = "web_scene s";
            sql.FROM(tableName);
            sql.LEFT_OUTER_JOIN("web_scene_group sg on sg.id = s.sceneGroupId");
            sql.LEFT_OUTER_JOIN("web_connection c on c.sceneId = s.id");
            sql.LEFT_OUTER_JOIN("web_device d on c.deviceId = d.id and d.deleteFlag = 0");
            sql.WHERE("s.ownerId = " + uid + " and " + dimensionName + " is not null and "
                    + valueName + " is not null " + filterDataSql);
        } else if (table.getDataTableType().equals(Constant.DataTable.HISTORY_DATA)) {
            tableName = "web_history_data hd";
            sql.FROM(tableName);
            sql.LEFT_OUTER_JOIN("web_variable v on hd.varId = v.id");
            sql.LEFT_OUTER_JOIN("web_scene s on s.id = hd.sceneId");
            sql.LEFT_OUTER_JOIN("web_connection c on c.sceneId = s.id");
            sql.LEFT_OUTER_JOIN("web_device d on d.id = c.deviceId and d.deleteFlag = 0");
            sql.WHERE("hd.ownerId = " + uid + " and " + dimensionName + " is not null and "
                    + valueName + " is not null " + filterDataSql);
        } else if (table.getDataTableType().equals(Constant.DataTable.HISTORY_ALARM)) {
            tableName = "web_history_alarm ha";
            sql.FROM(tableName);
            sql.LEFT_OUTER_JOIN("web_variable v on ha.varId = v.id");
            sql.LEFT_OUTER_JOIN("web_scene s on s.id = ha.sceneId");
            sql.LEFT_OUTER_JOIN("web_connection c on c.sceneId = s.id");
            sql.LEFT_OUTER_JOIN("web_device d on d.id = c.deviceId and d.deleteFlag = 0");
            sql.WHERE("ha.ownerId = " + uid + " and " + dimensionName + " is not null and "
                    + valueName + " is not null " + filterDataSql);
        }
        sql.GROUP_BY(dimensionName);
        return sql.toString();
    }

    /**
     * 获取报表过滤sql
     *
     * @param table
     * @param tableFilterValueList
     * @return: java.lang.String
     */
    public String getFilterDataSql(Table table, List<TableFilterValue> tableFilterValueList) {
        String sql = "";
        List<TableFilter> filters = table.getFilters();
        for (int j = 0; j < filters.size(); j++) {
            TableFilter filter = filters.get(j);
            Integer filterDataType = filter.getFilterDataType();
            Integer filterMode = filter.getFilterMode();
            // 过滤数据需要转换为list
            List<String> filterValue = tableFilterValueList.get(j).getFilterValue();
            String fieldName = getFieldName(table.getDataTableType(), filter.getFilterField());
            long currTime = System.currentTimeMillis();
            long requestTime = 0;
            if (filterMode.equals(Constant.FilterMode.EMPTY)) {
                sql += " and " + fieldName + " is null ";
            } else if (filterMode.equals(Constant.FilterMode.NOT_EMPTY)) {
                sql += " and " + fieldName + " is not null ";
            } else if (filterDataType.equals(Constant.TableFilterDataType.STRING_SINGLE)) {
                String tmp = null;
                if (filterMode.equals(Constant.FilterMode.CONTAIN)) {
                    tmp = " like ";
                } else if (filterMode.equals(Constant.FilterMode.NOT_CONTAIN)) {
                    tmp = " not like ";
                }
                if (!tableFilterValueList.isEmpty() && StringUtils.hasLength(tableFilterValueList.get(j).getFilterValue().get(0))) {
                    sql += " and " + fieldName + tmp + " concat('%',#{tableFilterValueList[" + j + "].filterValue[0" +
                            "]},'%') ";
                }
            } else if (filterDataType.equals(Constant.TableFilterDataType.STRING_MANY)) {
                String operate = null;
                if (filterMode.equals(Constant.FilterMode.EQUAL)) {
                    operate = " = ";
                } else if (filterMode.equals(Constant.FilterMode.NOT_EQUAL)) {
                    operate = " <> ";
                }
                boolean isFirst = true;
                for (int i = 0; i < filterValue.size(); i++) {
                    if (!"".equals(filterValue.get(i))) {
                        if (isFirst) {
                            sql += " and( " + fieldName + operate + "  #{tableFilterValueList[" + j + "].filterValue[" + i + "]} ";
                            isFirst = false;
                        } else {
                            sql += " or " + fieldName + operate + "  #{tableFilterValueList[" + j + "].filterValue[" + i + "]} ";
                        }
                    }
                }
                if (!isFirst) {
                    sql += " ) ";
                }
            } else if (filterDataType.equals(Constant.TableFilterDataType.VALUE_SINGEL)) {
                String operate = null;
                if (filterMode.equals(Constant.FilterMode.EQUAL)) {
                    operate = " = ";
                } else if (filterMode.equals(Constant.FilterMode.NOT_EQUAL)) {
                    operate = " <> ";
                } else if (filterMode.equals(Constant.FilterMode.LESS_THAN_OR_EQUAL)) {
                    operate = " <= ";
                } else if (filterMode.equals(Constant.FilterMode.MORE_THAN_OR_EQUAL)) {
                    operate = " >= ";
                } else if (filterMode.equals(Constant.FilterMode.LESS_THAN)) {
                    operate = " < ";
                } else if (filterMode.equals(Constant.FilterMode.MORE_THAN)) {
                    operate = " > ";
                }
                if (!filterValue.isEmpty() && StringUtils.hasLength(filterValue.get(0))) {
                    sql += " and " + fieldName + operate + "  #{tableFilterValueList[" + j + "].filterValue[0]} ";
                }
            } else if (filterDataType.equals(Constant.TableFilterDataType.VALUE_DOUBLE)) {
                if (filterMode.equals(Constant.FilterMode.RANGE)) {
                    if (!filterValue.isEmpty() && StringUtils.hasLength(filterValue.get(0))) {
                        sql += " and " + fieldName + " >= #{tableFilterValueList[" + j + "].filterValue[0]} ";
                    } else if (!filterValue.isEmpty() && StringUtils.hasLength(filterValue.get(1))) {
                        sql += " and " + fieldName + " <= #{tableFilterValueList[" + j + "].filterValue[1]} ";
                    }
                }
            } else if (filterDataType.equals(Constant.TableFilterDataType.TIME_FIXED)) {
                if (filterMode.equals(Constant.FilterMode.EQUAL)) {
                    if (!filterValue.isEmpty() && StringUtils.hasLength(filterValue.get(0))) {
                        sql += " and " + fieldName + " = #{tableFilterValueList[" + j + "].filterValue[0]} ";
                    }
                }
            } else if (filterDataType.equals(Constant.TableFilterDataType.TIME_SINGLE)) {
                if (filterMode.equals(Constant.FilterMode.NOT_EQUAL)) {
                    if (!filterValue.isEmpty() && StringUtils.hasLength(filterValue.get(0))) {
                        sql += " and " + fieldName + " <> #{tableFilterValueList[" + j + "].filterValue[0]} ";
                    }
                } else if (filterMode.equals(Constant.FilterMode.LESS_THAN_OR_EQUAL)) {
                    if (!filterValue.isEmpty() && StringUtils.hasLength(filterValue.get(0))) {
                        sql += " and " + fieldName + " <= #{tableFilterValueList[" + j + "].filterValue[0]} ";
                    }
                } else if (filterMode.equals(Constant.FilterMode.MORE_THAN_OR_EQUAL)) {
                    if (!filterValue.isEmpty() && StringUtils.hasLength(filterValue.get(0))) {
                        sql += " and " + fieldName + " >= #{tableFilterValueList[" + j + "].filterValue[0]} ";
                    }
                }
            } else if (filterDataType.equals(Constant.TableFilterDataType.TIME_DOUBLE)) {
                if (filterMode.equals(Constant.FilterMode.RANGE)) {
                    if (!filterValue.isEmpty() && StringUtils.hasLength(filterValue.get(0))) {
                        sql += " and " + fieldName + " >= #{tableFilterValueList[" + j + "].filterValue[0]} ";
                    } else if (!filterValue.isEmpty() && StringUtils.hasLength(filterValue.get(1))) {
                        sql += " and " + fieldName + " <= #{tableFilterValueList[" + j + "].filterValue[1]} ";
                    }
                }
            } else {
                requestTime = getTableRequestTime(filterDataType, currTime, requestTime);
                Date date = new Date(requestTime);
                String format = DateUtils.format(date);
                sql += " and " + fieldName + " >= " + format + " ";
            }
        }
        return sql;
    }

    /**
     * 获取报表过滤请求时间
     *
     * @param filterDataType
     * @param currTime
     * @param requestTime
     * @return: long
     */
    private long getTableRequestTime(Integer filterDataType, long currTime, long requestTime) {
        if (filterDataType.equals(Constant.TableFilterDataType.HALF_AN_HOUR)) {
            requestTime = currTime - 30 * 60 * 1000;
        } else if (filterDataType.equals(Constant.TableFilterDataType.HOUR)) {
            requestTime = currTime - 30 * 60 * 1000;
        } else if (filterDataType.equals(Constant.TableFilterDataType.THREE_HOUR)) {
            requestTime = currTime - 30 * 60 * 1000;
        } else if (filterDataType.equals(Constant.TableFilterDataType.SIX_HOUR)) {
            requestTime = currTime - 30 * 60 * 1000;
        } else if (filterDataType.equals(Constant.TableFilterDataType.ONE_DAY)) {
            requestTime = currTime - 30 * 60 * 1000;
        } else if (filterDataType.equals(Constant.TableFilterDataType.TWO_DAY)) {
            requestTime = currTime - 30 * 60 * 1000;
        } else if (filterDataType.equals(Constant.TableFilterDataType.SEVEN_DAY)) {
            requestTime = currTime - 30 * 60 * 1000;
        } else if (filterDataType.equals(Constant.TableFilterDataType.ONE_MONTH)) {
            requestTime = currTime - 30 * 60 * 1000;
        } else if (filterDataType.equals(Constant.TableFilterDataType.THREE_MONTH)) {
            requestTime = currTime - 30 * 60 * 1000;
        } else if (filterDataType.equals(Constant.TableFilterDataType.SIX_MONTH)) {
            requestTime = currTime - 30 * 60 * 1000;
        } else if (filterDataType.equals(Constant.TableFilterDataType.ONE_YEAR)) {
            requestTime = currTime - 30 * 60 * 1000;
        }
        return requestTime;
    }

    /**
     * 获取报表字段在sql查询语句中名称
     *
     * @param dataTableType
     * @param filterField
     * @return: java.lang.String
     */
    public String getFieldName(Integer dataTableType, Integer filterField) {
        if (dataTableType.equals(Constant.DataTable.USER)) {
            if (filterField.equals(Constant.UserTable.USERNAME)) {
                return "u.username";
            } else if (filterField.equals(Constant.UserTable.PHONE)) {
                return "u.phone";
            } else if (filterField.equals(Constant.UserTable.ROLE)) {
                return "r.name";
            } else if (filterField.equals(Constant.UserTable.IS_PHONE_CHECKED)) {
                return "u.isPhoneChecked";
            } else if (filterField.equals(Constant.UserTable.CREATE_DATE_MONTH)) {
                return "DATE_FORMAT(u.createDate,'%Y-%m')";
            } else if (filterField.equals(Constant.UserTable.CREATE_DATE_WEEK)) {
                return "DATE_FORMAT(u.createDate,'%Y-%u')";
            } else if (filterField.equals(Constant.UserTable.CREATE_DATE_DAY)) {
                return "DATE_FORMAT(u.createDate,'%Y-%m-%d')";
            } else if (filterField.equals(Constant.UserTable.CREATE_DATE_HOUR)) {
                return "DATE_FORMAT(u.createDate,'%Y-%m-%d %H')";
            } else if (filterField.equals(Constant.UserTable.CREATE_DATE_MINUTE)) {
                return "DATE_FORMAT(u.createDate,'%Y-%m-%d %H:%i')";
            }
        } else if (dataTableType.equals(Constant.DataTable.DEVICE)) {
            if (filterField.equals(Constant.DeviceTable.DEVICE_NAME)) {
                return "d.deviceName";
            } else if (filterField.equals(Constant.DeviceTable.DEVICE_CLUSTER)) {
                return "dc.name";
            } else if (filterField.equals(Constant.DeviceTable.DEVICE_STATUS)) {
                return "d.online";
            } else if (filterField.equals(Constant.DeviceTable.CREATE_DATE_MONTH)) {
                return "DATE_FORMAT(d.createDate,'%Y-%m')";
            } else if (filterField.equals(Constant.DeviceTable.CREATE_DATE_WEEK)) {
                return "DATE_FORMAT(d.createDate,'%Y-%u')";
            } else if (filterField.equals(Constant.DeviceTable.CREATE_DATE_DAY)) {
                return "DATE_FORMAT(d.createDate,'%Y-%m-%d')";
            } else if (filterField.equals(Constant.DeviceTable.CREATE_DATE_HOUR)) {
                return "DATE_FORMAT(d.createDate,'%Y-%m-%d %H')";
            } else if (filterField.equals(Constant.DeviceTable.CREATE_DATE_MINUTE)) {
                return "DATE_FORMAT(d.createDate,'%Y-%m-%d %H:%i')";
            } else if (filterField.equals(Constant.DeviceTable.MAC_ADDR)) {
                return "d.macAddr";
            } else if (filterField.equals(Constant.DeviceTable.PROVINCE)) {
                return "d.province";
            } else if (filterField.equals(Constant.DeviceTable.CITY)) {
                return "d.city";
            } else if (filterField.equals(Constant.DeviceTable.REGION)) {
                return "d.region";
            }
        } else if (dataTableType.equals(Constant.DataTable.SCENE)) {
            if (filterField.equals(Constant.SceneTable.SCENE_NAME)) {
                return "s.name";
            } else if (filterField.equals(Constant.SceneTable.CREATE_DATE_MONTH)) {
                return "DATE_FORMAT(s.createDate,'%Y-%m')";
            } else if (filterField.equals(Constant.SceneTable.CREATE_DATE_WEEK)) {
                return "DATE_FORMAT(s.createDate,'%Y-%u')";
            } else if (filterField.equals(Constant.SceneTable.CREATE_DATE_DAY)) {
                return "DATE_FORMAT(s.createDate,'%Y-%m-%d')";
            } else if (filterField.equals(Constant.SceneTable.CREATE_DATE_HOUR)) {
                return "DATE_FORMAT(s.createDate,'%Y-%m-%d %H')";
            } else if (filterField.equals(Constant.SceneTable.CREATE_DATE_MINUTE)) {
                return "DATE_FORMAT(s.createDate,'%Y-%m-%d %H:%i')";
            } else if (filterField.equals(Constant.SceneTable.SCENE_STATUS)) {
                return "s.state";
            } else if (filterField.equals(Constant.SceneTable.DEVICE_NAME)) {
                return "d.deviceName";
            } else if (filterField.equals(Constant.SceneTable.SCENE_GROUP)) {
                return "sg.name";
            }
        } else if (dataTableType.equals(Constant.DataTable.HISTORY_DATA)) {
            if (filterField.equals(Constant.HistoryDataTable.PRODUCT_TIME_MONTH)) {
                return "DATE_FORMAT(hd.productTime,'%Y-%m')";
            } else if (filterField.equals(Constant.HistoryDataTable.PRODUCT_TIME_WEEK)) {
                return "DATE_FORMAT(hd.productTime,'%Y-%u')";
            } else if (filterField.equals(Constant.HistoryDataTable.PRODUCT_TIME_DAY)) {
                return "DATE_FORMAT(hd.productTime,'%Y-%m-%d')";
            } else if (filterField.equals(Constant.HistoryDataTable.PRODUCT_TIME_HOUR)) {
                return "DATE_FORMAT(hd.productTime,'%Y-%m-%d %H')";
            } else if (filterField.equals(Constant.HistoryDataTable.PRODUCT_TIME_MINUTE)) {
                return "DATE_FORMAT(hd.productTime,'%Y-%m-%d %H:%i')";
            } else if (filterField.equals(Constant.HistoryDataTable.VALUE)) {
                return "hd.value";
            } else if (filterField.equals(Constant.HistoryDataTable.VAR_NAME)) {
                return "v.name";
            } else if (filterField.equals(Constant.HistoryDataTable.SCENE_NAME)) {
                return "s.name";
            } else if (filterField.equals(Constant.HistoryDataTable.DEVICE_NAME)) {
                return "d.deviceName";
            }
        } else if (dataTableType.equals(Constant.DataTable.HISTORY_ALARM)) {
            if (filterField.equals(Constant.HistoryAlarmTable.TRIGGER_TIME_MONTH)) {
                return "DATE_FORMAT(ha.productTime,'%Y-%m')";
            } else if (filterField.equals(Constant.HistoryAlarmTable.TRIGGER_TIME_WEEK)) {
                return "DATE_FORMAT(ha.productTime,'%Y-%u')";
            } else if (filterField.equals(Constant.HistoryAlarmTable.TRIGGER_TIME_DAY)) {
                return "DATE_FORMAT(ha.productTime,'%Y-%m-%d')";
            } else if (filterField.equals(Constant.HistoryAlarmTable.TRIGGER_TIME_HOUR)) {
                return "DATE_FORMAT(ha.productTime,'%Y-%m-%d %H')";
            } else if (filterField.equals(Constant.HistoryAlarmTable.TRIGGER_TIME_MINUTE)) {
                return "DATE_FORMAT(ha.productTime,'%Y-%m-%d %H:%i')";
            } else if (filterField.equals(Constant.HistoryAlarmTable.TRIGGER_VALUE)) {
                return "ha.value";
            } else if (filterField.equals(Constant.HistoryAlarmTable.ALARM_TYPE)) {
                return "ha.alarmType";
            } else if (filterField.equals(Constant.HistoryAlarmTable.TRIGGER_VAR)) {
                return "v.name";
            } else if (filterField.equals(Constant.HistoryAlarmTable.SCENE_NAME)) {
                return "s.name";
            } else if (filterField.equals(Constant.HistoryAlarmTable.DEVICE_NAME)) {
                return "d.deviceName";
            } else if (filterField.equals(Constant.HistoryAlarmTable.ALARM_TEXT)) {
                return "ha.alarmText";
            }
        }
        throw new BusinessException(ResponseResult.TABLE_FILTER_FIELD_TYPE_ERROR);
    }

    /**
     * 获取报表列表对比项数值sql(列转行)
     *
     * @param valueType
     * @param fieldName
     * @return: java.lang.String
     */
    private String getTableTableValueSql(Integer valueType, String compareName, String fieldName,
                                         List<String> tableCompareField) {
        String sql = "";
        for (int i = 0; i < tableCompareField.size(); i++) {
            if (valueType.equals(Constant.TableValueType.COUNT)) {
                sql += ", count(IF(" + compareName + "= #{tableCompareField[" + i + "]}," + fieldName + ", null)) as " +
                        "column" + i + " ";
            } else if (valueType.equals(Constant.TableValueType.COUNT_UNIQUE)) {
                sql += ", count(distinct IF(" + compareName + "= #{tableCompareField[" + i + "]}," + fieldName + ", " +
                        "null)) as  column" + i + " ";
            } else if (valueType.equals(Constant.TableValueType.AVG)) {
                sql += ", avg(IF(" + compareName + "= #{tableCompareField[" + i + "]}," + fieldName + ", " +
                        "null)) as  column" + i + " ";
            } else if (valueType.equals(Constant.TableValueType.MAX)) {
                sql += ", max(IF(" + compareName + "= #{tableCompareField[" + i + "]}," + fieldName + ", " +
                        "null)) as  column" + i + " ";
            } else if (valueType.equals(Constant.TableValueType.MIN)) {
                sql += ", min(IF(" + compareName + "= #{tableCompareField[" + i + "]}," + fieldName + ", " +
                        "null)) as  column" + i + " ";
            } else if (valueType.equals(Constant.TableValueType.SUM)) {
                sql += ", sum(IF(" + compareName + "= #{tableCompareField[" + i + "]}," + fieldName + ", " +
                        "null)) as  column" + i + " ";
            } else if (valueType.equals(Constant.TableValueType.FIRST)) {
                sql += ", IF(" + compareName + "= #{tableCompareField[" + i + "]}," +
                        "substring_index(group_concat(" + fieldName + " order by " + fieldName +
                        " asc),',',1),null) as  column" + i + " ";
            } else if (valueType.equals(Constant.TableValueType.LAST)) {
                sql += ", IF(" + compareName + "= #{tableCompareField[" + i + "]}," +
                        "substring_index(group_concat(" + fieldName + " order by " + fieldName +
                        " desc),',',1),null) as  column" + i + " ";
            }
        }

        return sql;
    }

    /**
     * 获取报表数值sql
     *
     * @param valueType
     * @param fieldName
     * @return: java.lang.String
     */
    private String getTableValueSql(Integer valueType, String fieldName) {
        String sql = "";
        if (valueType.equals(Constant.TableValueType.COUNT)) {
            sql += " count(" + fieldName + ") as dimension";
        } else if (valueType.equals(Constant.TableValueType.COUNT_UNIQUE)) {
            sql += " count(distinct " + fieldName + ") as dimension";
        } else if (valueType.equals(Constant.TableValueType.AVG)) {
            sql += " avg(" + fieldName + ") as dimension";
        } else if (valueType.equals(Constant.TableValueType.MAX)) {
            sql += " max(" + fieldName + ") as dimension";
        } else if (valueType.equals(Constant.TableValueType.MIN)) {
            sql += " min(" + fieldName + ") as dimension";
        } else if (valueType.equals(Constant.TableValueType.SUM)) {
            sql += " sum(" + fieldName + ") as dimension";
        } else if (valueType.equals(Constant.TableValueType.FIRST)) {
            sql += " substring_index(group_concat(" + fieldName + " order by " + fieldName + " asc),',',1) as " +
                    "dimension";
        } else if (valueType.equals(Constant.TableValueType.LAST)) {
            sql += " substring_index(group_concat(" + fieldName + " order by " + fieldName + " desc),',',1) as " +
                    "dimension";
        }
        return sql;
    }

    /**
     * 获取报表列表动态比较字段(列转行）
     *
     * @param table
     * @param tableFilterValueList
     * @return: java.lang.String
     */
    public String getTableTableCompareFieldSql(Table table, List<TableFilterValue> tableFilterValueList) {
        SQL sql = new SQL();
        // 获取当前用户id
        Object principal = SecurityContextHolder.getContext().getAuthentication().getPrincipal();
        UserDetailsImpl userDetailsImpl = (UserDetailsImpl) principal;
        Long uid = userDetailsImpl.getUser().getId();

        // 获取维度字段名称
        String dimensionName = getFieldName(table.getDataTableType(), table.getDimension());
        // 获取对比字段名称
        String compareName = getFieldName(table.getDataTableType(), table.getCompare());
        // 获取数值字段名称
        String valueName = getFieldName(table.getDataTableType(), table.getValue());
        String tableName = null;
        // 获取过滤条件sql
        String filterDataSql = getFilterDataSql(table, tableFilterValueList);
        sql.SELECT_DISTINCT(compareName);
        if (table.getDataTableType().equals(Constant.DataTable.USER)) {
            tableName = "web_user u";
            sql.FROM(tableName);
            sql.LEFT_OUTER_JOIN(" web_role r on u.rid = r.id");
            sql.WHERE("((u.deleteFlag = '' and u.parentId = " + uid + " ) or u.id = " + uid + ") and " + valueName +
                    " is" + " not null and " +
                    dimensionName + " is not null and " + compareName + " is not null " + filterDataSql);
        } else if (table.getDataTableType().equals(Constant.DataTable.DEVICE)) {
            tableName = "web_device d";
            sql.FROM(tableName);
            sql.LEFT_OUTER_JOIN("web_user_device_cluster udc on udc.deviceClusterId = d.deviceClusterId and d.deleteFlag = 0");
            sql.LEFT_OUTER_JOIN("web_user u on u.id = udc.userId");
            sql.LEFT_OUTER_JOIN("web_device_cluster dc on d.deviceClusterId = dc.id");
            sql.WHERE(" udc.userId = " + uid + " and " + valueName + " is not null and "
                    + dimensionName + " is not null  and " + compareName + " is not null " + filterDataSql);
        } else if (table.getDataTableType().equals(Constant.DataTable.SCENE)) {
            tableName = "web_scene s";
            sql.FROM(tableName);
            sql.LEFT_OUTER_JOIN("web_scene_group sg on sg.id = s.sceneGroupId");
            sql.LEFT_OUTER_JOIN("web_connection c on c.sceneId = s.id");
            sql.LEFT_OUTER_JOIN("web_device d on c.deviceId = d.id and d.deleteFlag = 0");
            sql.WHERE("s.ownerId = " + uid + " and " + valueName + " is not null and "
                    + dimensionName + " is not null  and " + compareName + " is not null " + filterDataSql);
//            if(!table.getValue().equals(Constant.SceneTable.DEVICE_NAME)){
//               sql.AND().GROUP_BY(valueName);
//            }
        } else if (table.getDataTableType().equals(Constant.DataTable.HISTORY_DATA)) {
            tableName = "web_history_data hd";
            sql.FROM(tableName);
            sql.LEFT_OUTER_JOIN("web_variable v on hd.varId = v.id");
            sql.LEFT_OUTER_JOIN("web_scene s on s.id = hd.sceneId");
            sql.LEFT_OUTER_JOIN("web_connection c on c.sceneId = s.id");
            sql.LEFT_OUTER_JOIN("web_device d on d.id = c.deviceId and d.deleteFlag = 0");
            sql.WHERE("hd.ownerId = " + uid + " and " + valueName + " is not null and "
                    + dimensionName + " is not null  and " + compareName + " is not null " + filterDataSql);
        } else if (table.getDataTableType().equals(Constant.DataTable.HISTORY_ALARM)) {
            tableName = "web_history_alarm ha";
            sql.FROM(tableName);
            sql.LEFT_OUTER_JOIN("web_variable v on ha.varId = v.id");
            sql.LEFT_OUTER_JOIN("web_scene s on s.id = ha.sceneId");
            sql.LEFT_OUTER_JOIN("web_connection c on c.sceneId = s.id");
            sql.LEFT_OUTER_JOIN("web_device d on d.id = c.deviceId and d.deleteFlag = 0");
            sql.WHERE("ha.ownerId = " + uid + " and " + valueName + " is not null and "
                    + dimensionName + " is not null  and " + compareName + " is not null " + filterDataSql);
        }
        return sql.toString();
    }
}